--
--    Description:
--       Update end_date of FND_USER to disable and keep the original start/end dates at description field.
--
--    Revision:
--       1st    25-Nov-2005    Yau Wai Yan
--
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
SET SERVEROUTPUT ON SIZE 1000000;

prompt
prompt *************************************************************************************************
prompt * This script is to disable NGO specific users. Please specify the business group name (Cxxx).
prompt *************************************************************************************************
prompt
prompt Parameters:
accept Action prompt "Input 'Disable Cxxx Users' to disable NGO Specific users: "

DECLARE
   l_bg_name                        VARCHAR2(100);
   l_description                    VARCHAR2(1000);
   l_start_date                     DATE;
   l_end_date                       DATE;
   l_para_string                    VARCHAR2(30);
   
   -- data table
   CURSOR cur_data (x_bg_name varchar2) is
      SELECT data.*
      FROM   fnd_user data
      WHERE  data.user_name LIKE x_bg_name||'%'
      ORDER BY data.user_id;
   
BEGIN

   l_para_string := UPPER('&Action');
   l_bg_name := SUBSTR(l_para_string, 9, 4);

   IF SUBSTR(l_para_string, 1, 8) = 'DISABLE ' AND SUBSTR(l_para_string, 13) = ' USERS' THEN
      BEGIN
         SELECT SUBSTR(name, 4, 4)
         INTO l_bg_name
         FROM per_business_groups
         WHERE name = 'BG_'||l_bg_name;
      EXCEPTION
         WHEN OTHERS THEN
         dbms_output.put_line('Invalid Business Group Name. Program exit.');
         return;
      END;

      dbms_output.put_line('bg_name = ' || l_bg_name || '.');
   ELSE
      dbms_output.put_line('Invalid parameter string. Program exit.');
      return;
   END IF;


   FOR sel IN cur_data (l_bg_name) LOOP
      -- clear all temporary variables
      l_description                := NULL;
      l_start_date                 := NULL;
      l_end_date                   := NULL;

      
      /* -- Validation and Mapping -- */
      l_description := sel.description||'@@@@@'||to_char(sel.start_date, 'ddmmyyyy')||'#####'||to_char(sel.end_date, 'ddmmyyyy');
      l_start_date := to_date('01011900', 'ddmmyyyy');
      l_end_date := to_date('01011900', 'ddmmyyyy');


      /* -- Upload data -- */ 
      BEGIN
        UPDATE fnd_user
        SET start_date = l_start_date, 
            end_date = l_end_date,
            description = l_description
        WHERE user_id = sel.user_id;

        dbms_output.put_line('Update '||sel.user_name);
      EXCEPTION
      WHEN others THEN
         rollback;
         dbms_output.put_line('Update User Error. user_id = ' || sel.user_id);
         return;
      END;
  
   END LOOP;

   COMMIT;

END;
/
